package yongxin.controller.kingdeeReport;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.tag.core.easyui.TagUtil;
import org.jeecgframework.web.system.service.SystemService;
import yongxin.utils.ExcelUtils;

/**
 * @Title: Controller
 * @Description: 采购管理
 * @author zhangdaihao
 * @date 2015-02-05 14:03:13
 * @version V1.0
 * 
 */
@Controller
@RequestMapping("/purchasingController")
public class PurchasingController extends BaseController
{
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(PurchasingController.class);

	@Autowired
	private SystemService systemService;

	private String message;

	public String getMessage()
	{
		return message;
	}

	public void setMessage(String message)
	{
		this.message = message;
	}

	/**
	 * 
	 * 合同（应付）
	 * 
	 * @return
	 */
	@RequestMapping(params = "rPContractList")
	public ModelAndView rPContractList(HttpServletRequest request)
	{

		return new ModelAndView("yongxin/kingdeeReport/rPContractList");

	}

	/**
	 * easyui 合同应付
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 * @param user
	 */

	@RequestMapping(params = "datagrid")
	public void datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{
		String FDate_begin = request.getParameter("FDate_begin");
		String FDate_end = request.getParameter("FDate_end");
		
		String FContractNo = request.getParameter("FContractNo");
		
		String FProductID2_DSPName = request.getParameter("FProductID2.DSPName");
		String FItemID40828_DSPName = request.getParameter("FItemID40828.DSPName");
		String FItemID40891_DSPName = request.getParameter("FItemID40891.DSPName");
		
		
		String sql = " SELECT t_RPContract.Fstatus,t_RPContract.FContractName,t_RPContract.FDate, ";
		sql += " t_RPContract.FContractNo,t_Currency.FName AS FCurrencyID_DSPName,t_Item1.FName AS FCustomer_DSPName,t_ICItem1.FNumber AS FProductID2_DSPName,t_ICItem1.FName AS FItemID40828_DSPName,t_ICItem1.FModel AS FItemID40891_DSPName, ";
		sql += " t_Measureunit.FName AS FUnitID_DSPName,t_rpContractEntry.FQuantity,t_rpContractEntry.FPriceFor,t_rpContractEntry.FTaxPriceFor,t_RPContract.FTotalAmountFor,t_rpContractEntry.FFactPriceFor, ";
		sql += " t_rpContractEntry.FAmountIncludeTaxFor,t_AuxItem.FNumber AS FAuxPropID_FNDName,t_AuxItem.FName AS FAuxPropID_DSPName,t_rpContractEntry.FBusinessDiscountRate,t_rpContractEntry.FBusinessDiscountFor,t_rpContractEntry.FBusinessDiscount, ";
		sql += " t_User1.FName AS FBillerID_DSPName,t_User.FName AS FChecker_DSPName,t_RPContract.FCheckDate,t_rpContractEntry.FAmountIncludeTax,t_rpContractEntry.FTaxRate,t_rpContractEntry.FTaxFor, ";
		sql += " t_Emp.FName AS FEmployee_DSPName,t_Department.FName AS FDepartment_DSPName ";
		sql += " FROM  RemoteServer.AIS2009.dbo.t_RPContract  LEFT JOIN RemoteServer.AIS2009.dbo.t_rpContractEntry  ON t_RPContract.FContractID=t_rpContractEntry.FContractID ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Currency  ON t_RPContract.FCurrencyID=t_Currency.FCurrencyID AND t_Currency.FCurrencyID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_ITem t_Item1 ON t_RPContract.FCustomer=t_Item1.FItemID AND t_Item1.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Department  ON t_RPContract.FDepartment=t_Department.FItemID AND t_Department.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Emp  ON t_RPContract.FEmployee=t_Emp.FItemID AND t_Emp.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_User t_User1 ON t_RPContract.FBillerID=t_User1.FUserID AND t_User1.FUserID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_User  ON t_RPContract.FChecker=t_User.FUserID AND t_User.FUserID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON t_rpContractEntry.FProductID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_AuxItem  ON t_rpContractEntry.FAuxPropID=t_AuxItem.FItemID AND t_AuxItem.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Measureunit  ON t_rpContractEntry.FUnitID=t_Measureunit.FItemID AND t_Measureunit.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Measureunit t_Measureunit1 ON t_RpContractEntry.FSecUnitID=t_Measureunit1.FItemID AND t_Measureunit1.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Item t_ICItem1_FBaseUnitID ON t_ICItem1.FUnitID=t_ICItem1_FBaseUnitID.FItemID AND t_ICItem1_FBaseUnitID.FItemID<>0 ";
		sql += " WHERE t_RPContract.Fstatus<2 AND isnull(t_RPContract.FChecker,0)>0 AND t_RPContract.FClassTypeID=1000020 "; 
		//sql += "  and (  ";
		
		if (StringUtil.isNotEmpty(FDate_begin))
		{
			sql += " and t_RPContract.FDate >=  " + "'" + FDate_begin + "'";
		}
		if (StringUtil.isNotEmpty(FDate_end))
		{
			sql += " and t_RPContract.FDate <=  " + "'" + FDate_end + "'";
		}
		
		if (StringUtil.isNotEmpty(FContractNo))
		{
			sql += " and  t_RPContract.FContractNo like " + "'%" + FContractNo + "%'";
		}
        
		if (StringUtil.isNotEmpty(FProductID2_DSPName))
		{
			sql += " and t_ICItem1.FNumber like  " + "'%" + FProductID2_DSPName + "%'";
		}
		
		if (StringUtil.isNotEmpty(FItemID40828_DSPName))
		{
			sql += " and  t_ICItem1.FName like " + "'%" + FItemID40828_DSPName + "%'";
		}
		
		if (StringUtil.isNotEmpty(FItemID40891_DSPName))
		{
			sql += " and   t_ICItem1.FModel like " + "'%" + FItemID40891_DSPName + "%'";
		}
		


		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){
				sql += " order by " + orderField + dataGrid.getOrder();
			}
		}
        
		
		List<Map<String, Object>> maplist = null;
		List<Map<String, Object>> allmaplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());

		} catch (Exception ex)
		{

		}
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
		// 数量和金额合计
		//Double sum_qty = 0.00;
		Double sum_qty = 0.00;
		BigDecimal sum_amount = new BigDecimal("0.0000");
		BigDecimal sum_amount2 = new BigDecimal("0.0000");
		for (Map<String, Object> map : maplist)
		{ 
			sum_qty += (Double) map.get("FQuantity");   
			
			sum_amount = sum_amount.add((BigDecimal) map.get("FTotalAmountFor"));  
			sum_amount2 = sum_amount.add((BigDecimal) map.get("FAmountIncludeTaxFor"));  
		}
		dataGrid.setFooter("Fstatus:合计," + "FQuantity:" + sum_qty + ",FTotalAmountFor:" + sum_amount +  ",FAmountIncludeTaxFor:" + sum_amount2);
		TagUtil.datagrid(response, dataGrid);
	}
	
	
	
	/**
	 * 
	 * 導出
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export")
	public void export(HttpServletRequest request,HttpServletResponse response, DataGrid dataGrid) {
		
		String FDate_begin = request.getParameter("FDate_begin");
		String FDate_end = request.getParameter("FDate_end");
		
		String FContractNo = request.getParameter("FContractNo");
		
		String FProductID2_DSPName = request.getParameter("FProductID2.DSPName");
		String FItemID40828_DSPName = request.getParameter("FItemID40828.DSPName");
		String FItemID40891_DSPName = request.getParameter("FItemID40891.DSPName");
		
		
		String sql = " SELECT t_RPContract.Fstatus,t_RPContract.FContractName,Convert(varchar(50),t_RPContract.FDate,23) FDate, ";
		sql += " t_RPContract.FContractNo,t_Currency.FName AS FCurrencyID_DSPName,t_Item1.FName AS FCustomer_DSPName,t_ICItem1.FNumber AS FProductID2_DSPName,t_ICItem1.FName AS FItemID40828_DSPName,t_ICItem1.FModel AS FItemID40891_DSPName, ";
		sql += " t_Measureunit.FName AS FUnitID_DSPName,t_rpContractEntry.FQuantity,t_rpContractEntry.FPriceFor,t_rpContractEntry.FTaxPriceFor,t_RPContract.FTotalAmountFor,t_rpContractEntry.FFactPriceFor, ";
		sql += " t_rpContractEntry.FAmountIncludeTaxFor,t_AuxItem.FNumber AS FAuxPropID_FNDName,t_AuxItem.FName AS FAuxPropID_DSPName,t_rpContractEntry.FBusinessDiscountRate,t_rpContractEntry.FBusinessDiscountFor,t_rpContractEntry.FBusinessDiscount, ";
		sql += " t_User1.FName AS FBillerID_DSPName,t_User.FName AS FChecker_DSPName,Convert(varchar(50),t_RPContract.FCheckDate,23) FCheckDate,t_rpContractEntry.FAmountIncludeTax,t_rpContractEntry.FTaxRate,t_rpContractEntry.FTaxFor, ";
		sql += " t_Emp.FName AS FEmployee_DSPName,t_Department.FName AS FDepartment_DSPName ";
		sql += " FROM  RemoteServer.AIS2009.dbo.t_RPContract  LEFT JOIN RemoteServer.AIS2009.dbo.t_rpContractEntry  ON t_RPContract.FContractID=t_rpContractEntry.FContractID ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Currency  ON t_RPContract.FCurrencyID=t_Currency.FCurrencyID AND t_Currency.FCurrencyID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_ITem t_Item1 ON t_RPContract.FCustomer=t_Item1.FItemID AND t_Item1.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Department  ON t_RPContract.FDepartment=t_Department.FItemID AND t_Department.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Emp  ON t_RPContract.FEmployee=t_Emp.FItemID AND t_Emp.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_User t_User1 ON t_RPContract.FBillerID=t_User1.FUserID AND t_User1.FUserID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_User  ON t_RPContract.FChecker=t_User.FUserID AND t_User.FUserID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON t_rpContractEntry.FProductID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_AuxItem  ON t_rpContractEntry.FAuxPropID=t_AuxItem.FItemID AND t_AuxItem.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Measureunit  ON t_rpContractEntry.FUnitID=t_Measureunit.FItemID AND t_Measureunit.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Measureunit t_Measureunit1 ON t_RpContractEntry.FSecUnitID=t_Measureunit1.FItemID AND t_Measureunit1.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Item t_ICItem1_FBaseUnitID ON t_ICItem1.FUnitID=t_ICItem1_FBaseUnitID.FItemID AND t_ICItem1_FBaseUnitID.FItemID<>0 ";
		sql += " WHERE t_RPContract.Fstatus<2 AND isnull(t_RPContract.FChecker,0)>0 AND t_RPContract.FClassTypeID=1000020 "; 
		//sql += "  and (  ";
		
		if (StringUtil.isNotEmpty(FDate_begin))
		{
			sql += " and t_RPContract.FDate >=  " + "'" + FDate_begin + "'";
		}
		if (StringUtil.isNotEmpty(FDate_end))
		{
			sql += " and t_RPContract.FDate <=  " + "'" + FDate_end + "'";
		}
		
		if (StringUtil.isNotEmpty(FContractNo))
		{
			sql += " and  t_RPContract.FContractNo like " + "'%" + FContractNo + "%'";
		}
        
		if (StringUtil.isNotEmpty(FProductID2_DSPName))
		{
			sql += " and t_ICItem1.FNumber like  " + "'%" + FProductID2_DSPName + "%'";
		}
		
		if (StringUtil.isNotEmpty(FItemID40828_DSPName))
		{
			sql += " and  t_ICItem1.FName like " + "'%" + FItemID40828_DSPName + "%'";
		}
		
		if (StringUtil.isNotEmpty(FItemID40891_DSPName))
		{
			sql += " and   t_ICItem1.FModel like " + "'%" + FItemID40891_DSPName + "%'";
		}
		


		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){
				sql += " order by " + orderField + dataGrid.getOrder();
			}
		}
        
		
		List<Map<String, Object>> maplist = null;

		try
		{
			maplist = systemService.findForJdbc(sql);
			List<String[]> list = new ArrayList<String[]>();
			
			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FContractName");
				arr[1] = (String) map.get("FDate");
				arr[2] = (String) map.get("FContractNo");
				arr[3] = (String) map.get("FCurrencyID_DSPName");
				arr[4] = (String) map.get("FCustomer_DSPName");
				arr[5] = (String) map.get("FProductID2_DSPName");
				arr[6] = (String) map.get("FItemID40828_DSPName");
				arr[7] = (String) map.get("FItemID40891_DSPName");
				arr[8] = (String) map.get("FUnitID_DSPName");
				arr[9] = String.valueOf((Double) map.get("FQuantity"));
				arr[10] = String.valueOf((Double) map.get("FPriceFor"));
				arr[11] = String.valueOf((Double) map.get("FTaxPriceFor"));
				arr[12] = String.valueOf((BigDecimal) map.get("FTotalAmountFor"));
				arr[13] = String.valueOf((Double) map.get("FFactPriceFor"));
				arr[14] = String.valueOf((BigDecimal) map.get("FAmountIncludeTaxFor"));
				arr[15] = (String) map.get("FBillerID_DSPName");
				arr[16] = (String) map.get("FChecker_DSPName");
				arr[17] = (String) map.get("FCheckDate");
				arr[18] = (String) map.get("FEmployee_DSPName");
				arr[19] = (String) map.get("FDepartment_DSPName");
				list.add(arr);
				 
			}
		
	
			String[] columnNames = new String[] { "合同名称", "合同日期", "合同号", "币别","核算项目","产品代码","产品名称","规格型号","计量单位","数量","不含税单价","含税单价","总金额","实际单价","价税合计","制单人","审核人","审核日期","业务员","部门"};
	       String titleName = "合同（应付）.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

	        ExcelUtils.create(request, list, columnNames, titleName, fOut);
			
		} catch (Exception ex)
		{

		}
		
	}

}
